<?php

/**
 * 备份数据库的扩展类
 */

namespace org;

class Baksql
{
    private $config = [];
    private $handler;
    private $tables = array(); //需要备份的表
    private $begin; //开始时间
    private $error; //错误信息

    public function __construct($config)
    {
        $config['path'] = './export/Backups/'.date('Ymd').'/';//默认目录
//        $config['path'] = ROOT_PATH . 'public' . DS . 'backup' . DS . 'data/'; //默认目录
        !file_exists($config['path']) && mkdir($config['path'], 0777, true);

        //$config["sqlbakname"]=date("YmdHis",time()).".sql";//默认保存文件
        //$config["sqlbakname"] = date("Y-m-d", time()) . "日备份" . ".sql"; //默认保存文件
        $config["sqlbakname"] = date("YmdHis", time()) . "备份" . ".sql"; //默认保存文件
        $config["sqlbaktmpname"] = date("YmdHis", time()) . "restoretmp" . ".sql"; //还原时临时保存文件

        $this->config = $config;
        $this->begin = microtime(true);
        header("Content-type: text/html;charset=utf-8");
        $this->connect();
    }
    //首次进行pdo连接
    private function connect()
    {
        try {
            $this->handler = new \PDO(
                "{$this->config['type']}:host={$this->config['hostname']};port={$this->config['hostport']};dbname={$this->config['database']};",
                $this->config['username'],
                $this->config['password'],
                array(
                    \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$this->config['charset']};",
                    \PDO::ATTR_ERRMODE =>  \PDO::ERRMODE_EXCEPTION,
                    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC
                )
            );
        } catch (\PDOException $e) {
            die("Error!: " . $e->getMessage() . "<br/>");
        }
    }
    /**
     * 查询
     * @param string $sql
     * @return mixed
     */
    private function query($sql = '')
    {
        $stmt = $this->handler->query($sql);
        $stmt->setFetchMode(\PDO::FETCH_NUM);
        $list = $stmt->fetchAll();
        return $list;
    }
    /**
     * 获取全部表
     * @param string $dbName
     * @return array
     */
    private function get_dbname($dbName = '*')
    {
        $sql = 'SHOW TABLES';
        $list = $this->query($sql);
        $tables = array();
        foreach ($list as $value) {
            $tables[] = $value[0];
        }
        return $tables;
    }
    /**
     * 获取表定义语句
     * @param string $table
     * @return mixed
     */
    private function get_dbhead($table = '')
    {
        $sql = "SHOW CREATE TABLE `{$table}`";
        $ddl = $this->query($sql)[0][1] . ';';
        return $ddl;
    }
    /**
     * 获取表数据
     * @param string $table
     * @return mixed
     */
    private function get_dbdata($table = '')
    {
        $sql = "SHOW COLUMNS FROM `{$table}`";
        $list = $this->query($sql);
        //字段
        $columns = '';
        //需要返回的SQL
        $query = '';
        foreach ($list as $value) {
            $columns .= "`{$value[0]}`,";
        }
        $columns = substr($columns, 0, -1);
        $data = $this->query("SELECT * FROM `{$table}`");
        $insertSqlPrex = "INSERT INTO `{$table}` ({$columns}) VALUES";

        foreach ($data as $value) {
//            var_dump($value);die;
            $dataSql = '';
//             foreach ($value as $v) {
//                 $v = addslashes(excelTrim($v));
//                 //$dataSql .= "'{$v}',";
//
//                 //$v = str_replace("'", "\'", $v);
//                 $dataSql .= "'" . $v . "', ";
//             }
//            foreach($value as &$item){
//                if($item == NUll){
//                    $item = '';
//                }
//            }
//            $value = array_map('addslashes', $value); //给预定义字符加反斜杠
            //cp循环次数
            $cp_loop_time = 0;
            foreach ($value as $v) {
//                var_dump(strpos($list[$cp_loop_time][1], 'int'));
                $str = strpos($list[$cp_loop_time][1], 'int') !== false || strpos($list[$cp_loop_time][1], 'double') !== false
                    || strpos($list[$cp_loop_time][1], 'float') !== false;
                if ($str) {
//                    var_dump($v.'其他');
//                    var_dump($v === NULL);
                    if ($v === NULL) {
                        $dataSql .= "NULL,";
                    } else {
                        //$v = addslashes($v);
                        $dataSql .= $v.",";
                    }
                } else {
                    //$v = addslashes($v);
//                    var_dump($v.'字符串类型');
                    $dataSql .= "'{$v}',";
                }
                $cp_loop_time++;
            }
            
            $dataSql = substr($dataSql, 0, -1);
            //$dataSql = substr($dataSql, 0, strlen($dataSql) - 2);
//            var_dump($dataSql);
            $query .= "({$dataSql}),";
        }
//        var_dump($query);die;
        if ($query) {
            $query = rtrim($query, ',');
            return $insertSqlPrex . $query . ";\r\n";
        } 
        
    }

    /**
     * 写入文件
     * @param array $tables
     * @param array $ddl
     * @param array $data
     */
    private function writeToFile($tables = array(), $ddl = array(), $data = array(),$name = '')
    {
        $str = "/*\r\nMySQL Database Backup Tools\r\n";
        $str .= "Server:{$this->config['hostname']}:{$this->config['hostport']}\r\n";
        $str .= "Database:{$this->config['database']}\r\n";
        $str .= "Data:" . date('Y-m-d H:i:s', time()) . "\r\n*/\r\n";
        $str .= "SET FOREIGN_KEY_CHECKS=0;\r\n";
        $i = 0;
        foreach ($tables as $table) {
            $str .= "-- ----------------------------\r\n";
            $str .= "-- Table structure for {$table}\r\n";
            $str .= "-- ----------------------------\r\n";
            $str .= "DROP TABLE IF EXISTS `{$table}`;\r\n";
            $str .= $ddl[$i] . "\r\n";
            $str .= "-- ----------------------------\r\n";
            $str .= "-- Records of {$table}\r\n";
            $str .= "-- ----------------------------\r\n";
            $str .= $data[$i] . "\r\n";
            $i++;
        }
        if(!empty($name)){
            $sqlbakname = $name.'.sql';
        }else{
            $sqlbakname = $this->config['sqlbakname'];
        }
        $filename = iconv('utf-8', 'gb2312', $this->config['path'] . $sqlbakname);
        if (file_put_contents($filename, $str)) {
            if(!empty($name)){
                $path = $this->config['path'] ;
            }else{
                $path = $this->config['path']. $sqlbakname ;
            }
            return $path;
//            return show('0', "备份成功,花费时间" . round(microtime(true) - $this->begin, 2) . 's', [], '200');
        } else {
//            return show('2', "备份失败", [], '200');
            return show('2', "备份失败", [], '200');
        }
    }

    /**
     * 设置要备份的表
     * @param array $tables
     */
    private function setTables($tables = array())
    {
        if (!empty($tables) && is_array($tables)) {
            //备份指定表
            $this->tables = $tables;
        } else {
            //备份全部表
            $this->tables = $this->get_dbname();
        }
    }

    /**
     * 备份
     * @param array $tables
     * @return bool
     */
    public function backup($tables = array(),$name = '')
    {
        //存储表定义语句的数组
        $ddl = array();
        //存储数据的数组
        $data = array();
        $this->setTables($tables);
        if (!empty($this->tables)) {
            foreach ($this->tables as $table) {
                $ddl[] = $this->get_dbhead($table);
                $data[] = $this->get_dbdata($table);
            }
            //开始写入
            return $this->writeToFile($this->tables, $ddl, $data,$name);
        } else {
             $this->error = '数据库中没有表!';
             return false;
//            return show('2', '备份失败，数据库中没有表!', [], '200');
        }
    }

    /**
     * 还原前的备份一个临时sql，防止出错
     * @param array $tables
     * @return bool
     */
    public function backuptmp($tables = array())
    {
        //存储表定义语句的数组
        $ddl = array();
        //存储数据的数组
        $data = array();
        $this->setTables($tables);
        if (!empty($this->tables)) {
            foreach ($this->tables as $table) {
                $ddl[] = $this->get_dbhead($table);
                $data[] = $this->get_dbdata($table);
            }

            //开始写入
            return $this->writeToTmpFile($this->tables, $ddl, $data);
        } else {
            // $this->error = '数据库中没有表!';
            // return false;
            return false;
        }
    }

    /**
     * 写入临时文件（还原时用）
     * @param array $tables
     * @param array $ddl
     * @param array $data
     */
    private function writeToTmpFile($tables = array(), $ddl = array(), $data = array())
    {
        $str = "/*\r\nMySQL Database Backup Tools\r\n";
        $str .= "Server:{$this->config['hostname']}:{$this->config['hostport']}\r\n";
        $str .= "Database:{$this->config['database']}\r\n";
        $str .= "Data:" . date('Y-m-d H:i:s', time()) . "\r\n*/\r\n";
        $str .= "SET FOREIGN_KEY_CHECKS=0;\r\n";
        $i = 0;
        foreach ($tables as $table) {
            $str .= "-- ----------------------------\r\n";
            $str .= "-- Table structure for {$table}\r\n";
            $str .= "-- ----------------------------\r\n";
            $str .= "DROP TABLE IF EXISTS `{$table}`;\r\n";
            $str .= $ddl[$i] . "\r\n";
            $str .= "-- ----------------------------\r\n";
            $str .= "-- Records of {$table}\r\n";
            $str .= "-- ----------------------------\r\n";
            $str .= $data[$i] . "\r\n";
            $i++;
        }

        $filename = iconv('utf-8', 'gb2312', $this->config['path'] . $this->config['sqlbaktmpname']);
        if (file_put_contents($filename, $str)) {
            return $this->config['sqlbaktmpname'];
        } else {
            return false;
        }
    }

    /**
     * 错误信息
     * @return mixed
     */
    public function getError()
    {
        return $this->error;
    }

    /**
     * 还原
     * @param string $fileName
     * @return mixed
     */
    /* 这里是后台同步，直接调用这一个接口就可以*/
    public function restore($fileName = '',$path = '')
    {
        if(empty($path)){
            $path = $this->config['path'];
        }
        $path = iconv('utf-8', 'gb2312', $path);
        if (!file_exists($path)) {
            // $this->error = 'SQL文件不存在!';
            // return false;
            return show('1', '还原失败，SQL文件不存在!', [], '200');
        } else {
            $sql = $this->parseSQL($path.'/'.$fileName.'.sql');
            try {
                set_time_limit(0);
                $this->handler->exec($sql);
                //echo '还原成功!花费时间', round(microtime(true) - $this->begin, 2) . 'ms';
//                file_put_contents("restore.json", 1);
                return success([], '还原成功,花费时间' . round(microtime(true) - $this->begin, 2) . 's');
//                return show('0', '还原成功,花费时间' . round(microtime(true) - $this->begin, 2) . 's', [], '200');
            } catch (\PDOException $e) {
                // $this->error = $e->getMessage();
                // return false;
                return error([], '还原失败' . $e->getMessage());
            }
        }
    }
    /*
    public function restore($sql)
    {
        try {
            set_time_limit(0);
            $this->handler->exec($sql);
            //echo '还原成功!花费时间', round(microtime(true) - $this->begin, 2) . 'ms';
            return success([], '还原成功,花费时间' . round(microtime(true) - $this->begin, 2) . 's');
//                return show('0', '还原成功,花费时间' . round(microtime(true) - $this->begin, 2) . 's', [], '200');
        } catch (\PDOException $e) {
            // $this->error = $e->getMessage();
            // return false;
            return error('还原失败' . $e->getMessage());
//                return show('1', '还原失败' . $e->getMessage(), [], '200');
        }
    }*/

//    public function restore($path = '')
//    {
////        $path = $this->config['path'] . $fileName;
//        $path = iconv('utf-8', 'gb2312', $path);
//
//        if (!file_exists($path)) {
//            // $this->error = 'SQL文件不存在!';
//            // return false;
//            return show('1', '还原失败，SQL文件不存在!', [], '200');
//        } else {
//
//            $sql = $this->parseSQL($path);
//            try {
//                $this->handler->exec($sql);
//                //echo '还原成功!花费时间', round(microtime(true) - $this->begin, 2) . 'ms';
//                return success([], '还原成功,花费时间' . round(microtime(true) - $this->begin, 2) . 's');
////                return show('0', '还原成功,花费时间' . round(microtime(true) - $this->begin, 2) . 's', [], '200');
//            } catch (\PDOException $e) {
//                // $this->error = $e->getMessage();
//                // return false;
//                return error('还原失败' . $e->getMessage());
////                return show('1', '还原失败' . $e->getMessage(), [], '200');
//            }
//        }
//    }

    /**
     * 还原（还原前先备份）
     * @param string $fileName
     * @return mixed
     */
    public function restore2($fileName = '')
    {
        $path = $this->config['path'] . $fileName;
        $path = iconv('utf-8', 'gb2312', $path);

        if (!file_exists($path)) {
            // $this->error = 'SQL文件不存在!';
            // return false;
            return show('1', '还原失败，SQL文件不存在!', [], '200');
        } else {
            $sql = $this->parseSQL($path);
            //dump($sql);die;
            try {
                $this->handler->exec($sql);

                return true;
                //return show('0', '还原成功,花费时间' . round(microtime(true) - $this->begin, 2) . 'ms', [], '200');
            } catch (\PDOException $e) {
                // $this->error = $e->getMessage();

                return false;
                //return show('1', '还原失败' . $e->getMessage(), [], '200');
            }
        }
    }

    /**
     * 解析SQL文件为SQL语句数组
     * @param string $path
     * @return array|mixed|string
     */
    private function parseSQL($path = '')
    {
        $sql = file_get_contents($path);
        $sql = explode("\r\n", $sql);

        //先消除--注释
        $sql = array_filter($sql, function ($data) {
            if (empty($data) || preg_match('/^--.*/', $data)) {
                return false;
            } else {
                return true;
            }
        });
        $sql = implode('', $sql);
        //删除/**/注释
        $sql = preg_replace('/\/\*.*\*\//', '', $sql);
        return $sql;
    }

    /**
     * 下载备份
     * @param string $fileName
     * @return array|mixed|string
     */
    public function downloadFile($fileName)
    {
        $filePath = $this->config['path'] . $fileName;
        $filePath = iconv('utf-8', 'gb2312', $filePath);

        if (file_exists($filePath)) {

            ob_end_clean();
            @header("Content-disposition:attachment;filename=" . basename($fileName));
            @header("Content-type:application/octet-stream");
            @header("Accept-Ranges: bytes");
            @header("Content-Length:" . filesize($filePath));
            @header("Pragma:no-cache");
            @header("Expires:0");
            readfile($filePath);

            //return show('0', '下载成功', [], '200');
        } else {

            return show('1', '文件有错误, 下载失败', [], '200');
            //$this->error = "文件有错误！";
        }
    }

    /**
     * 下载备份2
     * @param string $fileName
     * @return array|mixed|string
     */
    public function downloadFile2($fileName)
    {

        $filePath = $this->config['path'] . $fileName;
        $filePath = iconv('utf-8', 'gb2312', $filePath);

        $sql = file_get_contents($filePath);

        //下载文件存放的地址
        //$destDir = ROOT_PATH . 'public' . DS . 'download' . DS . 'data/'; //方案1：指定tp框架下目录
        switch (strtolower(PHP_OS)) {
            case "unix":
                break;
            case "solaris":
                break;
            case "aix":
                break;
            case "linux":
                $destDir = '/public/download/data/'; //指定tp框架下目录
//                $destDir = ROOT_PATH . '/public/download/data/'; //指定tp框架下目录

                break;
            default:    //WINNT
//                $pan = explode(':', ROOT_PATH)[0];
//                $pan = explode(':', ROOT_PATH)[0];
                $destDir = ':/evaluation_download/';  //指定盘符下目录
//                $destDir = $pan . ':/evaluation_download/';  //指定盘符下目录
                break;
        }

        //$destDir = dirname(dirname(dirname(ROOT_PATH))) . '/download/data_backup/'; //方案3：一级一级往上找到软件根目录   
        !file_exists($destDir) && mkdir($destDir, 0777, true);


        $dest1 = $destDir . $fileName;
        $dest = iconv('utf-8', 'gb2312', $dest1);

        if (file_put_contents($dest, $sql)) {
            return show('0', '下载成功', ['list' => '下载的文件路径：' . str_replace('/', '\\', $destDir)], '200');
        } else {
            return show('1', '下载失败', [], '200');
        }
    }


    /**
     * 获取文件是时间
     * @param string $file
     * @return string
     */
    private function getfiletime($file)
    {
        $path = $this->config['path'] . $file;
        $a = filemtime($path);
        $time = date("Y-m-d H:i:s", $a);
        return $time;
    }

    /**
     * 获取文件是时间
     * @param string $file
     * @return string
     */
    private function getfiledate($file)
    {
        $path = $this->config['path'] . $file;
        $a = filemtime($path);
        $date = date("Y-m-d", $a);
        return $date;
    }

    /**
     * 获取文件是大小
     * @param string $file
     * @return string
     */
    private function getfilesize($file)
    {
        $perms = stat($this->config['path'] . $file);
        $size = $perms['size'];
        $a    = ['B', 'KB', 'MB', 'GB', 'TB'];
        $pos  = 0;
        while ($size >= 100) {
            $size /= 1024;
            $pos++;
        }

        return round($size, 1) . $a[$pos];
    }

    /**
     * 获取文件列表
     * @param string $Order 级别
     * @return array
     */
    public function getfilelist($Order = 0)
    {
        $FilePath = $this->config['path'];
        //        print_r($FilePath);die;
        $FilePath = opendir($FilePath);
        //        $FilePath = scandir($FilePath);
        $FileAndFolderAyy = array();
        $i = 1;
        while (false !== ($filename = readdir($FilePath))) {
            if ($filename != "." && $filename != "..") {
                $i++;
                $FileAndFolderAyy[$i]['fileName'] = iconv('gb2312', 'utf-8',  $filename);
                $FileAndFolderAyy[$i]['time'] = $this->getfiletime($filename);
                $FileAndFolderAyy[$i]['date'] = $this->getfiledate($filename);
                $FileAndFolderAyy[$i]['size'] = $this->getfilesize($filename);
            }
        }

        if ($FileAndFolderAyy && $Order == 0) { //按时间降序排序
            $timeArr = array_column($FileAndFolderAyy, 'time');
            array_multisort($timeArr, SORT_DESC, $FileAndFolderAyy);
        } else {
            $timeArr = array_column($FileAndFolderAyy, 'time');
            array_multisort($timeArr, SORT_ASC, $FileAndFolderAyy);
        }

        return $FileAndFolderAyy;
    }

    /**
     * 删除文件
     * @param string fileName 文件名
     * @return array
     */
    public function delfilename($fileName)
    {
        $filePath = $this->config['path'] . $fileName;
        $filePath = iconv('utf-8', 'gb2312', $filePath);
        if (@unlink($filePath)) {
            return show('0', '删除成功', [], '200');
        } else {
            return show('1', '删除失败', [], '200');
        }
    }

    /**
     * 本地上传sql备份文件
     * @param string sqlFiles  SQL文件(支持多文件)
     * @return array
     */
    public function uploadbackfile($sqlFiles)
    {

        $upFileNameStr = '';
        $errorStr = '';

        print_r($sqlFiles);
        die();

        foreach ($sqlFiles as $file) {
            $fileName = iconv('utf-8', 'gbk', $file->getInfo()['name']);
            $info = $file->validate(['ext' => 'sql'])->move($this->config['path'], $fileName);
            if ($info) {
                // 成功上传后 获取上传信息               
                //echo $info->getExtension();                
                //echo $info->getFilename();
                $upFileNameStr .= $info->getFilename() . '、';
            } else {
                // 上传失败获取错误信息
                //echo $file->getError();

                $errorStr .= $file->getError();
            }
        }

        if ($errorStr) {
            return show('0', '上传失败', ['info' => '失败原因' . $errorStr], '200');
        }
        return show('0', '上传成功', ['info' => '成功上传' . $upFileNameStr], '200');
    }

    /**
     * 本地上传sql备份文件
     * @param string sqlFiles  SQL文件(单文件)
     * @return array
     */
    public function uploadbackfile2($file)
    {

        $upFileNameStr = '';
        $errorStr = '';

        $fileName = iconv('utf-8', 'gbk', $file->getInfo()['name']);
        $info = $file->validate(['ext' => 'sql'])->move($this->config['path'], $fileName);

        if ($info) {
            // 成功上传后 获取上传信息               
            //echo $info->getExtension();                
            //echo $info->getFilename();

            $upFileNameStr = $info->getFilename();
            $upFileNameStr = iconv('gb2312', 'utf-8', $upFileNameStr);
            return show('0', '上传成功', ['info' => '成功上传' . $upFileNameStr], '200');
        } else {
            // 上传失败获取错误信息
            //echo $file->getError();

            $errorStr .= $file->getError();

            if ($errorStr) {
                return show('0', '上传失败', ['info' => '失败原因' . $errorStr], '200');
            }
        }
    }
}
